package web.loaisp;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;



public class ConnDBLoaiSP {
	Connection connect = null;
	Statement stmt = null;
	ResultSet rs = null;
	/**
	 * Check Driver connect to MySQL
	 */
	protected void hasDriver() throws Exception {
		try {
			Class.forName("com.mysql.jdbc.Driver");
		} catch (ClassNotFoundException ex) {
			throw new Exception("Invalid Driver!!Please check this drver....");
		}
	}

	/**
	 * Function used to get the connection to the Database Step 1 - I check my
	 * connection or not!! Step 2 - If not, it will be null and initialization.
	 * Step 3 - Then it return
	 * 
	 * @return Connection
	 */
	protected Connection openConnect() throws Exception {
		if (connect == null) {
			hasDriver();
			String url = "jdbc:mysql://localhost/shoponline";
			// url_sql = "....";
			try {
				this.connect = DriverManager
						.getConnection(url, "root", "12345");
			} catch (SQLException e) {
				throw new Exception(e.getMessage()
						+ "Connect failed to database .... ");
			}
		}
		return connect;
	}

	/**
	 * Make a Statement to execute the SQL statement
	 * 
	 * @return Statement
	 */
	protected Statement getStatement() throws SQLException, Exception {
		if (stmt == null) {
			stmt = openConnect().createStatement();
		}
		return stmt;
	}

	/**
	 * Used to execute the Select statement
	 * 
	 * @param strSQL
	 *            Query VD: Select * from Employee
	 * @return ResultSet
	 */
	// thuc hien cac cau lenh cho data sanpham
		public ArrayList<LoaiSanPham> getAllLoaiSanPhams() throws Exception {
			ArrayList<LoaiSanPham> lst = new ArrayList<LoaiSanPham>();
			String strSQL = "select * from loaisp";
			try {
				rs = getStatement().executeQuery(strSQL);
				while (rs.next()) {
					String ms = rs.getString("maloaisp");
					String ten = rs.getString("tenloaisp");
					
					LoaiSanPham lsp = new LoaiSanPham(ms, ten);
					lst.add(lsp);
				}
			} catch (Exception e) {
				throw new Exception(e.getMessage() + " Error at : " + strSQL);
			}
			closeConnet();
			return lst;
		}

		public boolean insertNew(LoaiSanPham lsp) throws Exception {
			String sql = "insert into loaisp values(?,?)";
			PreparedStatement pst = openConnect().prepareStatement(sql);
			pst.setString(1, lsp.getMaloaisp());
			pst.setString(2, lsp.getTenloaisp());
		

			return pst.executeUpdate() > 0;

		}

		public boolean deleteProduct(String mlsp) throws Exception {
			String sql = "delete from loaisp where maloaisp=?";
			PreparedStatement pst = openConnect().prepareStatement(sql);
			pst.setString(1, mlsp);
			return pst.executeUpdate() > 0;
		}

		/**
		 * Used to execute the Insert, Update, Delete statement
		 * 
		 * @param strSQL
		 *            Query VD: Insert into TableName values ('??','??')
		 * @return The number of lines affected by the command
		 */
		public int executeUpdate(String strSQL) throws Exception {
			int result = 0;
			try {
				result = getStatement().executeUpdate(strSQL);
			} catch (Exception ex) {
				throw new Exception(ex.getMessage() + " Error at: " + strSQL);
			} finally {
				this.closeConnet();
			}
			return result;
		}

		public LoaiSanPham getLoaiSanPhamID(String maloaisp) throws Exception {
			String sql = "select * from loaisp where maloaisp=?";
			PreparedStatement pst = openConnect().prepareStatement(sql);
			pst.setString(1, maloaisp);
			ResultSet rs = pst.executeQuery();
			LoaiSanPham lsp = null;
			if (rs.next()) {
				String ms = rs.getString("maloaisp");
				String ten = rs.getString("tenloaisp");				
				lsp = new LoaiSanPham(ms, ten);
			}
			return lsp;
		}

		public boolean UpdateProduct(String mlsp, LoaiSanPham newlsp) throws Exception {
			String sql = "update loaisp set tenloaisp=? where maloaisp=?";
			PreparedStatement pst = openConnect().prepareStatement(sql);
			pst.setString(1, newlsp.getTenloaisp());		
			pst.setString(2, newlsp.getMaloaisp());
			return pst.executeUpdate() > 0;
		}

		
		/**
		 * A method to close the connection.
		 * 
		 * @throws SQLException
		 */
		public void closeConnet() throws SQLException {
			if (rs != null && !rs.isClosed())
				rs.close();
			if (stmt != null)
				stmt.close();
			if (connect != null)
				connect.close();
		}

}
